﻿Imports System.Data.OleDb

Public Class AquariumFishGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM AquariumFishView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM AquariumFish"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM Aquarium LEFT JOIN SiteUser " &
                   "ON Aquarium.UserID=SiteUser.ID " &
                   "WHERE Aquarium.ID=@ID"
        End Get
    End Property

    Public Overloads Sub DeleteByID(ByVal aquariumID As ULong, ByVal fishID As ULong)
        Dim sqlQuery As String = DeleteQuery & " WHERE AquariumID=@AquariumID AND FishID=@FishID"
        Using connection As New OleDbConnection(ConnectionString)
            Dim command As New OleDbCommand(sqlQuery, connection)
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@AquariumID", aquariumID)
            command.Parameters.AddWithValue("@FishID", fishID)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

    Public Overloads Function FindByID(ByVal aquariumID As ULong, ByVal fishID As ULong) As DataTable
        Dim sqlQuery As String = FindAllQuery & "  WHERE ID=@AquariumID AND FishID=@FishID"
        Dim queryParams As New OrderedDictionary()
        queryParams.Add("@AquariumID", aquariumID)
        queryParams.Add("@FishID", fishID)
        Return QueryTable(sqlQuery, queryParams)
    End Function

    Public Sub Insert(ByVal aquariumID As ULong, ByVal fishID As ULong,
                      ByVal total As Integer,
                      Optional ByVal photoID As ULong = 0)
        Dim sqlQuery As String
        If photoID = 0 Then
            sqlQuery = "INSERT INTO AquariumFish (AquariumID, FishID, Total) " &
                       "VALUES (@AquariumID, @FishID, @Total)"
        Else
            sqlQuery = "INSERT INTO AquariumFish (AquariumID, FishID, Total, PhotoID) " &
                       "VALUES (@AquariumID, @FishID, @Total, @PhotoID)"
        End If

        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                command.Parameters.AddWithValue("@FishID", fishID)
                command.Parameters.AddWithValue("@Total", total)
                If photoID <> 0 Then
                    command.Parameters.AddWithValue("@PhotoID", photoID)
                End If
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub

    Public Sub Update(ByVal aquariumID As ULong, ByVal fishID As ULong,
                      ByVal total As Integer, Optional ByVal photoID As ULong = 0)
        Dim sqlQuery As String
        If photoID = 0 Then
            sqlQuery = "UPDATE AquariumFish SET Total=@Total " &
                       "WHERE AquariumID=@AquariumID AND FishID=@FishID"
        Else
            sqlQuery = "UPDATE AquariumFish SET Total=@Total, PhotoID=@PhotoID " &
                       "WHERE AquariumID=@AquariumID AND FishID=@FishID"
        End If

        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Total", total)
                If photoID <> 0 Then
                    command.Parameters.AddWithValue("@PhotoID", photoID)
                End If
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                command.Parameters.AddWithValue("@FishID", fishID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
